11. Программирование в Excel

11.1 Зачем программировать в Excel

Создание приложений VBA в Excel, типичные ситуации

Excel - это наиболее часто используемое с точки зрения программирования приложение Office. По моему опыту преподавания курсов по программированию в Office, в подавляющем большинстве случаев сотрудников предприятий интересует, как автоматизировать выполнение операций именно в Excel. Чаще всего на предприятиях встречаются следующие ситуации:

• необходимо автоматизировать загрузку данных в таблицу Excel из базы данных, а затем в автоматическом режиме произвести обработку этой таблицы (расчеты, моделирование и т.п.), и представить эту информацию в стандартном виде. На практике, конечно, намного правильнее постараться перенести выполнение расчетов (группировка, расчет итогов по группам и т.п.) на сервер баз данных, но обычно у пользователей для этого нет ни необходимых знаний, ни прав для работы с сервером баз данных. Поэтому Excel в таких ситуациях остается незаменимым средством;
• вариант первой ситуации - приложение, работающее с базой данных, уже умеет генерировать отчеты в формате файлов Excel. Но со временем потребности в отчетах изменяются, появляется необходимость в новых отчетах или в изменении старых. Чаще всего в этом случае пользователи самостоятельно создают новые отчеты, используя данные из старых. Повторяющейся работы очень много, поэтому автоматизация таких операций бывает просто необходимой;
• очень часто пользователи, не имея возможности обратиться к профессиональным программистам, самостоятельно реализуют нужные им приложения в таблицах Excel. Во множестве организаций, например, финансовое планирование или составление смет ведется просто в виде множества файлов Excel (часто связанных между собой). Excel выполняет и роль базы данных, и роль клиентского приложения, и генератора отчетов. В таких ситуациях, конечно, опять-таки вопросы автоматизации стоят очень остро;
• формат файлов Excel удобен не только для вывода информации из базы данных, но и для загрузки введенной вручную информацию в базу данных. Часто на предприятиях информация из филиалов, подразделений, сотрудников и т.п. собирается в формате Excel. В результате со временем возникает вопрос - как автоматизировать процесс загрузки информации из Excel в базу данных;
• по моему опыту, на предприятиях часто возникает потребность в синхронизации информации между файлами Excel и базами данных (или другими файлами Excel, или файлами DBF и т.п. - момент не принципиальный). Например, как сделать так, чтобы при занесении пользователем информации в файл Excel эта информация сразу же добавилась в базу данных.

Приемы, необходимые для решения подобных задач, рассматриваются в данной главе. Надеемся, что после ее изучения проблем с тем, как их решать, у вас не возникнет.

С программной точки зрения Excel, в отличие от Word, чаще всего используется не как средство для вывода и редактирования данных, а для выполнения различных расчетов и отображения их в специальных форматах (график, сводная таблица и т.п.). Если же объем данных большой (например, нужно хранить информацию по заказчикам/договорам/поставкам, то есть смысл подумать о связке Excel плюс база данных (такая связка может быть очень удобной и производительной).

В отличие от Word, навигацию по книгам и листам Excel производить намного удобнее, поскольку у каждой ячейки есть свой адрес (и даже два адреса - в формате A1 и в формате R1C1). Кроме того, в Excel есть возможность присваивать имена диапазонам ячеек, что также очень удобно.

Иерархия стандартных объектов в Excel немного больше. Если в Word все построено вокруг трех объектов: Application - Document - Range, то в Excel появляется новый элемент - лист, поэтому главная его иерархия выглядит следующим образом: Application - Workbook (книга) - Worksheet (лист) - Range (диапазон).

В Excel предусмотрена очень богатая библиотека встроенных функций (статистических, финансовых, математических и т.п.), которые вполне можно использовать в приложениях. Часто именно наличие такой библиотеки функций оказывается решающим при выборе Excel в качестве платформы для построения приложения.

В Excel встроено несколько фактически внешних приложений, использование которых в может быть очень удобным. Примеры - Сводная таблица (объект PivotTable) - интегрированный в Excel OLAP-клиент приобретенной Microsoft фирмы Panorama Software, QueryTable - специальный объект для работы с информацией из базы данных, объект Chart - средство работы с диаграммами и т.п.

Получить этот учебник в формате PDF (Acrobat Reader) или DOC (Microsoft Word)